{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd,xlwings as xw"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 数据导入\n",
    "df=pd.read_excel(r'K:\\BaiduSyncdisk\\王振洋资料\\2.商贸分公司资料\\2月商贸分公司资料\\0.商贸分收入确认账务清理\\凭证管理（23.1-24.1）商贸分.xlsx')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "wb=xw.Book(r'K:\\BaiduSyncdisk\\王振洋资料\\2.商贸分公司资料\\2月商贸分公司资料\\0.商贸分收入确认账务清理\\凭证管理（23.1-24.1）商贸分.xlsx')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 填充缺失值\n",
    "df['科目名称'].fillna('',inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {},
   "outputs": [],
   "source": [
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 数据查询\n",
    "\n",
    "# 查询科目名称种含有收入的数据 ，将他们的制单日期和凭证字号取出来,去重\n",
    "df2=df[df['科目名称'].str.contains('收入')].loc[:,['制单日期','凭证字号']].drop_duplicates()\n",
    "df2['标志']='对'\n",
    "# a=xw.view(pd.merge(df,df2,on=['制单日期','凭证字号']).query('标志==\"对\"'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "df3=df[df['科目名称'].str.contains('收入')]\n",
    "df3=df3[~df3['摘要'].str.contains('结转期间损益')]\n",
    "df3=df3[~pd.isna(df3['贷方'])]  # 去掉贷方为空的行"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "df3['月份']=df3['制单日期'].str.split(\"-\").str[1]\n",
    "df3['科目名称2']=df3['科目名称'].str.split(\"-\").str[2]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>制单日期</th>\n",
       "      <th>凭证字号</th>\n",
       "      <th>凭证总金额</th>\n",
       "      <th>审核人</th>\n",
       "      <th>制单人</th>\n",
       "      <th>摘要</th>\n",
       "      <th>科目编码</th>\n",
       "      <th>科目名称</th>\n",
       "      <th>辅助项</th>\n",
       "      <th>借方</th>\n",
       "      <th>贷方</th>\n",
       "      <th>月份</th>\n",
       "      <th>科目名称2</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>58</th>\n",
       "      <td>2023-06-30</td>\n",
       "      <td>记-0019</td>\n",
       "      <td>65.0</td>\n",
       "      <td>兰霄</td>\n",
       "      <td>兰霄</td>\n",
       "      <td>技术代收到代理商物料款-装备置换物流费（铜陵代理商）</td>\n",
       "      <td>530199.0</td>\n",
       "      <td>营业外收入-其他营业外收入</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>65.00</td>\n",
       "      <td>06</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>66</th>\n",
       "      <td>2023-06-30</td>\n",
       "      <td>记-0023</td>\n",
       "      <td>1840.0</td>\n",
       "      <td>兰霄</td>\n",
       "      <td>梁艳丽</td>\n",
       "      <td>2023.06惠州代理商出库统计</td>\n",
       "      <td>50010201.0</td>\n",
       "      <td>主营业务收入-代理商物料收入-马甲（普通版）收入</td>\n",
       "      <td>招商部</td>\n",
       "      <td>NaN</td>\n",
       "      <td>407.77</td>\n",
       "      <td>06</td>\n",
       "      <td>马甲（普通版）收入</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>67</th>\n",
       "      <td>2023-06-30</td>\n",
       "      <td>记-0023</td>\n",
       "      <td>1840.0</td>\n",
       "      <td>兰霄</td>\n",
       "      <td>梁艳丽</td>\n",
       "      <td>2023.06惠州代理商出库统计</td>\n",
       "      <td>50010202.0</td>\n",
       "      <td>主营业务收入-代理商物料收入-保温箱（基础版）加大收入</td>\n",
       "      <td>招商部</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1106.80</td>\n",
       "      <td>06</td>\n",
       "      <td>保温箱（基础版）加大收入</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>68</th>\n",
       "      <td>2023-06-30</td>\n",
       "      <td>记-0023</td>\n",
       "      <td>1840.0</td>\n",
       "      <td>兰霄</td>\n",
       "      <td>梁艳丽</td>\n",
       "      <td>2023.06惠州代理商出库统计</td>\n",
       "      <td>50010204.0</td>\n",
       "      <td>主营业务收入-代理商物料收入-头盔（夏季）收入</td>\n",
       "      <td>招商部</td>\n",
       "      <td>NaN</td>\n",
       "      <td>271.84</td>\n",
       "      <td>06</td>\n",
       "      <td>头盔（夏季）收入</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>71</th>\n",
       "      <td>2023-06-30</td>\n",
       "      <td>记-0024</td>\n",
       "      <td>1200.0</td>\n",
       "      <td>兰霄</td>\n",
       "      <td>梁艳丽</td>\n",
       "      <td>2023.06玉溪代理商出库统计</td>\n",
       "      <td>50010101.0</td>\n",
       "      <td>主营业务收入-家政物料收入-保洁背包（到家）收入</td>\n",
       "      <td>家政</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1165.05</td>\n",
       "      <td>06</td>\n",
       "      <td>保洁背包（到家）收入</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4485</th>\n",
       "      <td>2024-01-31</td>\n",
       "      <td>记-0066</td>\n",
       "      <td>-874.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>王振洋</td>\n",
       "      <td>跑男商城1月退货-郑州</td>\n",
       "      <td>50010143.0</td>\n",
       "      <td>主营业务收入-家政物料收入-玻璃刮（到家）收入</td>\n",
       "      <td>郑州</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-6.18</td>\n",
       "      <td>01</td>\n",
       "      <td>玻璃刮（到家）收入</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4486</th>\n",
       "      <td>2024-01-31</td>\n",
       "      <td>记-0066</td>\n",
       "      <td>-874.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>王振洋</td>\n",
       "      <td>跑男商城1月退货-郑州</td>\n",
       "      <td>50010140.0</td>\n",
       "      <td>主营业务收入-家政物料收入-瓷砖刷（到家）收入</td>\n",
       "      <td>郑州</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-30.89</td>\n",
       "      <td>01</td>\n",
       "      <td>瓷砖刷（到家）收入</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4487</th>\n",
       "      <td>2024-01-31</td>\n",
       "      <td>记-0066</td>\n",
       "      <td>-874.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>王振洋</td>\n",
       "      <td>跑男商城1月退货-郑州</td>\n",
       "      <td>50010142.0</td>\n",
       "      <td>主营业务收入-家政物料收入-白云上水器（到家）收入</td>\n",
       "      <td>郑州</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-4.12</td>\n",
       "      <td>01</td>\n",
       "      <td>白云上水器（到家）收入</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4488</th>\n",
       "      <td>2024-01-31</td>\n",
       "      <td>记-0066</td>\n",
       "      <td>-874.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>王振洋</td>\n",
       "      <td>跑男商城1月退货-郑州</td>\n",
       "      <td>50010115.0</td>\n",
       "      <td>主营业务收入-家政物料收入-短袖（到家）收入</td>\n",
       "      <td>郑州</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-45.35</td>\n",
       "      <td>01</td>\n",
       "      <td>短袖（到家）收入</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4502</th>\n",
       "      <td>2024-01-31</td>\n",
       "      <td>记-0070</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>王振洋</td>\n",
       "      <td>1月2日乔松敏申请太原家政马甲退货17件，技术代付退货款</td>\n",
       "      <td>50010149.0</td>\n",
       "      <td>主营业务收入-家政物料收入-家政马甲（尊享版）收入</td>\n",
       "      <td>招商部</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-572.28</td>\n",
       "      <td>01</td>\n",
       "      <td>家政马甲（尊享版）收入</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1270 rows × 13 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "            制单日期    凭证字号   凭证总金额  审核人  制单人                            摘要  \\\n",
       "58    2023-06-30  记-0019    65.0   兰霄   兰霄   技术代收到代理商物料款-装备置换物流费（铜陵代理商）    \n",
       "66    2023-06-30  记-0023  1840.0   兰霄  梁艳丽              2023.06惠州代理商出库统计   \n",
       "67    2023-06-30  记-0023  1840.0   兰霄  梁艳丽              2023.06惠州代理商出库统计   \n",
       "68    2023-06-30  记-0023  1840.0   兰霄  梁艳丽              2023.06惠州代理商出库统计   \n",
       "71    2023-06-30  记-0024  1200.0   兰霄  梁艳丽              2023.06玉溪代理商出库统计   \n",
       "...          ...     ...     ...  ...  ...                           ...   \n",
       "4485  2024-01-31  记-0066  -874.0  NaN  王振洋                   跑男商城1月退货-郑州   \n",
       "4486  2024-01-31  记-0066  -874.0  NaN  王振洋                   跑男商城1月退货-郑州   \n",
       "4487  2024-01-31  记-0066  -874.0  NaN  王振洋                   跑男商城1月退货-郑州   \n",
       "4488  2024-01-31  记-0066  -874.0  NaN  王振洋                   跑男商城1月退货-郑州   \n",
       "4502  2024-01-31  记-0070     0.0  NaN  王振洋  1月2日乔松敏申请太原家政马甲退货17件，技术代付退货款   \n",
       "\n",
       "            科目编码                         科目名称  辅助项  借方       贷方  月份  \\\n",
       "58      530199.0                营业外收入-其他营业外收入  NaN NaN    65.00  06   \n",
       "66    50010201.0     主营业务收入-代理商物料收入-马甲（普通版）收入  招商部 NaN   407.77  06   \n",
       "67    50010202.0  主营业务收入-代理商物料收入-保温箱（基础版）加大收入  招商部 NaN  1106.80  06   \n",
       "68    50010204.0      主营业务收入-代理商物料收入-头盔（夏季）收入  招商部 NaN   271.84  06   \n",
       "71    50010101.0     主营业务收入-家政物料收入-保洁背包（到家）收入   家政 NaN  1165.05  06   \n",
       "...          ...                          ...  ...  ..      ...  ..   \n",
       "4485  50010143.0      主营业务收入-家政物料收入-玻璃刮（到家）收入   郑州 NaN    -6.18  01   \n",
       "4486  50010140.0      主营业务收入-家政物料收入-瓷砖刷（到家）收入   郑州 NaN   -30.89  01   \n",
       "4487  50010142.0    主营业务收入-家政物料收入-白云上水器（到家）收入   郑州 NaN    -4.12  01   \n",
       "4488  50010115.0       主营业务收入-家政物料收入-短袖（到家）收入   郑州 NaN   -45.35  01   \n",
       "4502  50010149.0    主营业务收入-家政物料收入-家政马甲（尊享版）收入  招商部 NaN  -572.28  01   \n",
       "\n",
       "             科目名称2  \n",
       "58             NaN  \n",
       "66       马甲（普通版）收入  \n",
       "67    保温箱（基础版）加大收入  \n",
       "68        头盔（夏季）收入  \n",
       "71      保洁背包（到家）收入  \n",
       "...            ...  \n",
       "4485     玻璃刮（到家）收入  \n",
       "4486     瓷砖刷（到家）收入  \n",
       "4487   白云上水器（到家）收入  \n",
       "4488      短袖（到家）收入  \n",
       "4502   家政马甲（尊享版）收入  \n",
       "\n",
       "[1270 rows x 13 columns]"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df3"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "df3.groupby(by=['月份','凭证字号','摘要'])['贷方'].sum().to_clipboard()"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "base",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.11.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
